package cn.tang.crm.service.action;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import cn.tang.crm.comm.UIMessage;
import cn.tang.crm.comm.Utils;

public class UserActionService extends ActionService {
	private static final Logger logger = Logger.getLogger(UserActionService.class);

	public String getClientNameOption(String username) {
		String sql = "select clientName from clientUser where username='" + username + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String clientName = rs.getString("clientName");
				ret += "<option value='" + clientName + "'>" + clientName + "</option>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String deleteClient(String id) {
		String sql = "delete from clientUser where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			int i = stmt.executeUpdate(sql);
			if (i == 1) {
				ret = UIMessage.DELETE_CLIENT_SUCCESS;
			} else {
				ret = UIMessage.DELETE_CLIENT_FAILURE;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String listAllClient(String username) {
		String sql = "select * from clientUser where username='" + username + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int index = 0;
			while (rs.next()) {
				index++;
				if (index % 2 == 0) {
					ret += "<tr class='alt'>";
				} else {
					ret += "<tr>";
				}
				String id = rs.getString("id");
//				ret += "<td>" + rs.getString("clientName") + "</td>";
				ret += "<td><span class='spanLinkClass' onclick='clientDetail(\"" + id + "\")'>"+rs.getString("clientName")+"</span></td>";
				ret += "<td>" + Utils.clientType(rs.getString("clientTypeID")) + "</td>";
				ret += "<td>" + rs.getFloat("salesAmount") + "</td>";
				ret += "<td>" + rs.getString("mobilePhone1") + "</td>";
				ret += "<td>" + rs.getString("mobilePhone2") + "</td>";
				ret += "<td>" + rs.getString("telephone") + "</td>";
				ret += "<td>" + rs.getString("email") + "</td>";
				ret += "<td>" + rs.getString("Region") + "</td>";
				ret += "<td>" + Utils.formatTime(rs.getLong("LastOrderTime")) + "</td>";
				ret += "<td>" + rs.getString("Profile") + "</td>";
				ret += "<td><span class='spanLinkClass' onclick='deleteClient(\"" + id + "\")'>delete</span></td>";
				ret += "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String clientNameIsExist(String clientName) {
		String sql = "select id from clientUser where clientName='" + clientName + "'";
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return "y";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return "n";
	}

	/**
	 * 
	 * @param clientName
	 * @param username
	 * @param clientType
	 * @param region
	 * @param mobilePhone1
	 * @param mobilePhone2
	 * @param telephone
	 * @param email
	 * @param profile
	 * @return create table clientUser(id varchar(36),username
	 *         varchar(100),clientName varchar(100),clientType
	 *         smallint,salesAmount float,region varchar(200),mobilePhone1
	 *         varchar(20),mobilePhone2 varchar(20),telephone
	 *         varchar(20),profile varchar(500),lastOrderTime int);
	 */
	public String addClient(String clientName, String username, String clientType, String region, String mobilePhone1, String mobilePhone2, String telephone,
			String email, String profile) {
		String sql = "insert into clientUser values(uuid(),'" + username + "','" + clientName + "'," + clientType + ",0,'" + region + "','" + mobilePhone1
				+ "','" + mobilePhone2 + "','" + telephone + "','" + email + "','" + profile + "',0)";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			int i = stmt.executeUpdate(sql);
			if (i == 1) {
				ret = UIMessage.ADD_CLIENT_SUCCESS;
			} else {
				ret = UIMessage.ADD_CLIENT_FAILURE;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	/**
	 * create table clientOrder(id varchar(36),username varchar(100),clientName
	 * varchar(100),orderTime int,payTime int,nameOfGoods
	 * varchar(200),specifications varchar(200),unitPrice float,salesNumber
	 * float,money float);
	 */
	public String listAllOrder(String username) {
		String sql = "select * from clientOrder where username='" + username + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int index = 0;
			while (rs.next()) {
				index++;
				if (index % 2 == 0) {
					ret += "<tr class='alt'>";
				} else {
					ret += "<tr>";
				}
				String id = rs.getString("id");
				int payStatus = rs.getInt("payStatus");
				ret += "<td>" + rs.getString("clientName") + "</td>";
				ret += "<td>" + Utils.formatTime(rs.getLong("orderTime")) + "</td>";
				ret += "<td>" + rs.getString("nameOfGoods") + "</td>";
				ret += "<td>" + rs.getString("specifications") + "</td>";
				ret += "<td>" + rs.getFloat("unitPrice") + "</td>";
				ret += "<td>" + rs.getFloat("salesNumber") + "</td>";
				ret += "<td>" + rs.getFloat("money") + "</td>";
				ret += "<td>" + Utils.payStatus(payStatus) + "</td>";
				if (payStatus == 1) {
					ret += "<td>&nbsp;</td>";
				} else {
					ret += "<td>" + Utils.formatTime(rs.getLong("payTime")) + "</td>";

				}
				ret += "<td><span class='spanLinkClass' onclick='deleteOrder(\"" + id + "\")'>delete</span></td>&nbsp;&nbsp;";
				if (payStatus == 1) {
					ret += "<span class='spanLinkClass' onclick='payOrder(\"" + id + "\")'>pay</span></td>";
				} else {
					ret += "<span class='spanLinkClass' onclick='cancelPayOrder(\"" + id + "\")'>cancel pay</span></td>";
				}
				ret += "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String payOrder(String id) {
		String sql = "update clientOrder set payStatus=2 , payTime=" + System.currentTimeMillis() + " where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			int i = stmt.executeUpdate(sql);
			if (i == 1) {
				ret = UIMessage.PAY_ORDER_SUCCESS;
			} else {
				ret = UIMessage.PAY_ORDER_FAILURE;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String cancelPayOrder(String id) {
		String sql = "update clientOrder set payStatus=1 , payTime=-1 where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			int i = stmt.executeUpdate(sql);
			if (i == 1) {
				ret = UIMessage.CANCEL_PAY_ORDER_SUCCESS;
			} else {
				ret = UIMessage.CANCEL_PAY_ORDER_FAILURE;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String deleteOrder(String id) {
		String sql = "delete from clientOrder where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			int i = stmt.executeUpdate(sql);
			if (i == 1) {
				ret = UIMessage.DELETE_ORDER_SUCCESS;
			} else {
				ret = UIMessage.DELETE_ORDER_FAILURE;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String addOrder(String username, String clientName, String orderTime, String nameOfGoods, String specifications, String unitPrice,
			String salesNumber, String money) {
		long orderTimeMillis = Utils.timeMills(orderTime);
		String sql = "insert into clientOrder values(uuid(),'" + username + "','" + clientName + "'," + orderTimeMillis + ",1,-1,'" + nameOfGoods + "','"
				+ specifications + "'," + unitPrice + "," + salesNumber + "," + money + ")";
		String updateClientStatus = "update clientuser set salesAmount=salesAmount+" + money + ", lastOrderTime=" + orderTimeMillis + " where clientName='"
				+ clientName + "' and username='" + username + "'";
		logger.debug(sql);
		logger.debug(updateClientStatus);
		List<String> sqlList = new ArrayList<String>();
		sqlList.add(sql);
		sqlList.add(updateClientStatus);
		String ret = "";
		try {
			connPool.batchUpdate(sqlList);
			ret = UIMessage.ADD_ORDER_SUCCESS;
		} catch (SQLException e) {
			ret = UIMessage.ADD_ORDER_FAILURE;
			e.printStackTrace();
		}
		return ret;
	}

}
